<%@page import="java.sql.*"%>
<%@page import="com.mysql.*"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Popular Movies</title>
<!--  DataTables CSS -->
<link rel="stylesheet" href="resources/mytheme/css/style.css">
<link rel="stylesheet" type="text/css"
	href="//cdn.datatables.net/1.10.0/css/jquery.dataTables.css">

<!-- jQuery -->
<script type="text/javascript" charset="utf8"
	src="//code.jquery.com/jquery-1.10.2.min.js"></script>
<!-- DataTables -->
<script type="text/javascript" charset="utf8"
	src="//cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>
<!-- Sets the JQuery/CSS to the tables named inventoryReport and userQueueReport  -->
<script type="text/javascript">
	$(document).ready(function() {
		$('#popularReport').dataTable();
		var table = $('#popularReport').DataTable();
		 
		table.order( [1, 'desc'] ).draw();
			
	});
	
	
</script>

</head>
<body>
<div id=header2>

	<div id="logo">
		<img src="resources/mytheme/Logo.png" alt="RentFlix">
	</div>
	
	<div id="searchNav">
		<form name="inputForm" method="get">
			
			<br>
			
		</form>
	</div>
	
	<div id="loginBar">
		<ul>
			<li><a class="lastlink" href="logout">Logout</a></li>
			<li><a class="lastlink" href="http://localhost:8080/MovieProject">Home</a> </li>
		
		
				
				<li><a href="reports">Admin: In-Stock</a> </li>
				<li><a href="userQueues">Admin: Queues</a> </li>
				<li><a href="popularMovieReport">Admin: Popular Movies</a> </li>
				<li><a href="newMovie">Admin: Add Movie</a> </li>
				<li><a href="CheckInRentals">Admin: Check-In</a> </li>
				
		</ul>
	</div>
	
</div>
<h1>-----------------------------</h1>
	

	<h1>Popular movies</h1>


	<%
		try {
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/moviedatabase", "dbuser",
					"dbpassword");

			Statement statement = connection.createStatement();

			ResultSet rs = statement
					.executeQuery("SELECT count(m.movieID) as numOrder, m.title from orders o join inventory i on o.inventoryid = i.inventoryid join movies m on m.movieID = i.movieID group by m.MovieID order by numOrder desc");
	%>
	<table id="popularReport"  class="display2">
		<thead>
			<tr>
				<td>Title</td>
				<td>Number of Orders</td>
			</tr>
		</thead>
		<tbody>
			<%
				while (rs.next()) {
			%>
			<tr>
				<td><%=rs.getString(2)%></td>
				<td id="numOrdered"><%=rs.getString(1)%></td>
			</tr>
			<%
				}
			%>
		</tbody>
	</table>
	<%
		} catch (Exception e) {
			out.println("Wrong entry" + e);
		}
	%>
</body>
</html>